package com.Util;

import com.Bean.Ticket;
import net.sf.json.JSONObject;

import java.sql.*;
import java.util.HashSet;
import java.util.Set;

public class TkDao extends DB {

    public static Set<Ticket> selectTkbyUsername(String username) {
        Set<Ticket> ticketSet = new HashSet<Ticket>();
        connector();
        try (Connection conn = DriverManager.getConnection(URL, USER, PWD)) {
            if (DB.isDebug()) {
                System.out.println("TkDao.selectTkbyUsername() processing");
            }
            Statement stmt = conn.createStatement(java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE, java.sql.ResultSet.CONCUR_READ_ONLY);


            String laststr=new String("");
            if(!UserDao.getUser(username).getStaff().equals("staff"))
            {
                laststr=" where username='" + username + "'";
            }
            ResultSet rs = stmt.executeQuery("select * from " + DBTK + laststr);

            int i = 0;
            pushrs2Set(rs, ticketSet);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return ticketSet;
    }

    public static Ticket selectTkbyId(String tkid) {
        Ticket tk = new Ticket();
        connector();
        try (Connection conn = DriverManager.getConnection(URL, USER, PWD)) {
            if (DB.isDebug()) {
                System.out.println("TkDao.selectTkbyTkid() processing");
            }
            Statement stmt = conn.createStatement(java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE, java.sql.ResultSet.CONCUR_READ_ONLY);

            ResultSet rs = stmt.executeQuery("select * from " + DBTK + " where id='" + tkid + "'");

            if(rs.next())  tk=rs2Class(rs);

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return tk;
    }

    private static void pushrs2Set(ResultSet rs, Set<Ticket> tkSet) {
        try {
            int i = 1;
            while (rs.next()) {
                Ticket tk = rs2Class(rs);
                tkSet.add(tk);
                if (DB.isDebug()) {
                    JSONObject jb = JSONObject.fromObject(tk);
                    String tojson = jb.toString();
                    System.out.println("ticket[" + i + "] for select");
                    System.out.println(tojson);
                    i++;
                }

            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static Ticket rs2Class(ResultSet rs) {

        Ticket tk = new Ticket();

        try {
            if(DB.isDebug())
            {
                System.out.println(rs.getString("id"));
            }
            tk.setUsername(rs.getString("username"));
            //messageSet[i].setPosttime(LocalDateTime.parse(rs.getString("posttime").replace(" ","T"));
            tk.setSubject(rs.getString("subject"));
            tk.setStatus(rs.getString("status"));
            tk.setLastupdate(rs.getString("lastupdate"));
            tk.setTypes(rs.getString("types"));
            tk.setId(String.format("%06",Integer.parseInt(rs.getString("id"))));
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return tk;
    }

    /**
     * 传入一个Ticket对象，将其数据插入到数据库中
     * @param ticket
     * @return 运行状态值,1表示成功, 0表示失败
     */
    public static int insertTk(Ticket ticket) {
        connector();
        try (Connection conn = DriverManager.getConnection(URL, USER, PWD)) {
            PreparedStatement stat = conn.prepareStatement("insert into `" + DBTK + "` (`username`, `types`,`subject`,`status`,`lastupdate`)" +
                    "values(?,?,?,?,now())");
            if (DB.isDebug()) {
                System.out.println("TkDao.updateUser() processing");
                JSONObject jb = JSONObject.fromObject(ticket);
                String tojson = jb.toString();
                System.out.println("tk for update");
                System.out.println(tojson);
            }

            stat.setString(1, ticket.getUsername());
            stat.setString(2, ticket.getTypes());
            stat.setString(3, ticket.getSubject());
            stat.setString(4, ticket.getStatus());

            if (DB.isDebug()) {
                System.out.println("Complete stat.setString()");
                System.out.println(stat);
            }
            int result = stat.executeUpdate();
            return result;

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }
    public static int updateTk(Ticket tk) {
        connector();
        try (Connection conn = DriverManager.getConnection(URL, USER, PWD)) {
            PreparedStatement stat = conn.prepareStatement("update  `" + DBTK +"`"
                    +" set username = ?, types = ?,subject = ?,status = ?,lastupdate = now()" +
                    "where id=?");
            if (DB.isDebug()) {
                System.out.println("TkDao.updateUser() processing");
                JSONObject jb = JSONObject.fromObject(tk);
                String tojson = jb.toString();
                System.out.println("tk for update");
                System.out.println(tojson);
            }

            stat.setString(1, tk.getUsername());
            stat.setString(2, tk.getTypes());
            stat.setString(3, tk.getSubject());
            stat.setString(4, tk.getStatus());
            stat.setString(5,tk.getId());

            if (DB.isDebug()) {
                System.out.println("Complete TkDao.updateUser()");
                System.out.println(stat);
            }
            int result = stat.executeUpdate();
            return result;

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }
}
